package com.warm.pump.frame.db;


import com.warm.pump.frame.gen.util.TypeCast;
import com.warm.pump.frame.util.SpringBeanUtil;
import com.warm.pump.module.work.bean.po.gen.WorkDb;
import com.warm.pump.module.work.dao.gen.WorkDbDao;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by guozj on 2017/10/11.
 */
public class DbUtil {
    private static final org.slf4j.Logger logger = LoggerFactory.getLogger(DbUtil.class);
    public static Map<String, DataSource> dataSourceMap = new HashMap<>();

    /**
     * 创建DataSource
     */
    @SuppressWarnings("unchecked")
    public static DataSource buildDataSource(WorkDb db) {
        try {
            DataSource ds = DataSourceEnum.match(db.getPoolType(), DataSourceEnum.DRUID).wrap(db);
            return ds;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    public static void clearDataSource(String dbKey){
        if(dataSourceMap.containsKey(dbKey)){
            dataSourceMap.remove(dbKey);
        }
    }

    public static DataSource getDataSource(String dbKey) {
        DataSource dataSource = dataSourceMap.get(dbKey);
        //long currTbime = System.currentTimeMillis();
        //logger.debug("连接数据库:" + dbKey);
        if (dataSource == null) {
            WorkDbDao workDbDao = SpringBeanUtil.getBean(WorkDbDao.class);
            Map<String, Object> condMap = new HashMap<>();
            condMap.put("dbKey", dbKey);
            WorkDb WorkDb = workDbDao.selectByMap(condMap);
            dataSource = buildDataSource(WorkDb);
            dataSourceMap.put(dbKey, dataSource);
        }
        return dataSource;
    }


    public static Map<String, Object> findOne(final String dbKey, String sql, Map<String, Object> paramMap) {
        List<Map<String, Object>> list = findList(dbKey, sql, paramMap);

        if (list == null || list.isEmpty()) {
            return null;
        }
        return list.get(0);
    }

    public static Map<String, Object> findOne(final String dbKey, String sql) {
        List<Map<String, Object>> list = findList(dbKey, sql, null);

        if (list == null || list.isEmpty()) {
            return null;
        }
        MapSqlParameterSource parameters = new MapSqlParameterSource();
        return list.get(0);
    }

    public static Long findCnt(final String dbKey, String sql, Map<String, Object> paramMap) {
        StringBuffer sqlBuffer = new StringBuffer("select count(1) cnt from (");
        sqlBuffer.append(sql);
        sqlBuffer.append(")m");
        Map<String, Object> qryMap = (Map<String, Object>) findOne(dbKey, sqlBuffer.toString(), paramMap);
        return (Long) qryMap.get("cnt");
    }

    public static Long findCnt(final String dbKey, String sql) {
        return findCnt(dbKey, sql, null);
    }


    public static List<Map<String, Object>> findListByPage(final String dbKey, String sql, int pageNo, int pageSize, Map<String, Object> paramMap) {
        StringBuffer sqlBuffer = new StringBuffer(sql);
        sqlBuffer.append(sql).append(" limit").append(pageNo).append(",").append(pageSize);
        return findList(dbKey, sqlBuffer.toString(), paramMap);
    }

    public static List<Map<String, Object>> findListByPage(final String dbKey, String sql, int pageNo, int pageSize) {
        StringBuffer sqlBuffer = new StringBuffer(sql);
        sqlBuffer.append(" limit ").append(pageNo).append(",").append(pageSize);
        return findList(dbKey, sqlBuffer.toString(), null);
    }

    public static JdbcTemplate getJdbcTemplate(String dbKey) {

        DataSource dataSource = getDataSource(dbKey);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        return jdbcTemplate;
    }

    public static List<Map<String, Object>> findList(final String dbKey, String sql, Map<String, Object> paramMap) {
        List<Map<String, Object>> list;
        JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);

        if (null != paramMap && paramMap.size() > 0) {
            NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
            list = namedParameterJdbcTemplate.queryForList(sql, paramMap);
        } else {
            list = jdbcTemplate.queryForList(sql);
        }
        return list;
    }

    public static List<Map<String, Object>> findList(final String dbKey, String sql) {
        return findList(dbKey, sql, null);
    }


    public static WorkDb getGetWorkDb(String dbKey) {
        WorkDbDao WorkDbDao = SpringBeanUtil.getBean(WorkDbDao.class);
        Map<String, Object> condMap = new HashMap<>();
        condMap.put("dbKey", dbKey);
        WorkDb WorkDb = WorkDbDao.selectByMap(condMap);
        return WorkDb;
    }

    /**
     * 获取数据库下所有表名
     * @param dbKey
     * @return
     */
    public static List<Map<String,Object>> getTablesFromDb(String dbKey,String qryName){

        WorkDb WorkDb = getGetWorkDb(dbKey);
        String sql = "";
        if("mysql".equals(WorkDb.getDbType())){
            sql = "select TABLE_NAME table_name,TABLE_COMMENT table_comment from 	information_schema.TABLES where TABLE_SCHEMA = '"
                    + WorkDb.getDbName()+"' and  TABLE_NAME like '%"+qryName+"%' order by table_name";
        }

        return  findList(dbKey,sql);
    }

    /**
     * 通过表名获取字段列表
     * @param dbKey
     * @param tableName
     * @return
     */
    public static List<Map<String, Object>> getTableConfigFromDB(String dbKey, String tableName) {

        WorkDb WorkDb = getGetWorkDb(dbKey);
        String sql = "";
        if ("mysql".equals(WorkDb.getDbType().toLowerCase())) {
            sql = "SELECT column_name, lower(data_type) as data_type,column_type as column_type,is_nullable as is_nullable,column_comment as column_comment,character_maximum_length,numeric_precision,numeric_scale " +
                    "FROM information_schema.columns " +
                    "WHERE table_schema = '" + WorkDb.getDbName() + "' and table_name = '" + tableName + "'";
        } else if ("postgresql".equals(WorkDb.getDbType().toLowerCase())) {
            sql = "SELECT a.attnum,a.attname AS column_name,lower(t.typname) AS data_type,a.attlen AS length,a.atttypmod-4 AS lengthvar,a.attnotnull AS notnull from pg_class c,pg_attribute a,pg_type t where a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and c.relname='" + tableName + "'";
        }

        return findList(dbKey, sql);
    }

    /**
     * 通过表名获取select语句
     * @param dbKey
     * @param qryName
     * @return
     */
    public static String getSelectSqlByTableName(String dbKey,String qryName){
        StringBuffer selectSql = new StringBuffer("select ");
        List<Map<String, Object>> tableConfigFromDB = getTableConfigFromDB(dbKey,qryName);
        int i = 0;
        for (Map<String, Object> map : tableConfigFromDB) {
            String columnName = TypeCast.getString(map.get("column_name"));
            selectSql.append(columnName);
            if(i == tableConfigFromDB.size()-1){
                selectSql.append("\nfrom ").append(qryName).append("\nwhere 1=1");
            }else{
                selectSql.append(",\n");
            }

            i++;
        }

        return selectSql.toString();
    }

    public static String getInsertSqlByTableName(String dbKey, String tableName) {
        StringBuffer insertSql = new StringBuffer(" insert into ").append(tableName).append("(");

        StringBuffer valueSql = new StringBuffer("values(");
        List<Map<String, Object>> tableConfigFromDB = getTableConfigFromDB(dbKey, tableName);


        int i = 0;
        for (Map<String, Object> map : tableConfigFromDB) {
            String columnName = TypeCast.getString(map.get("column_name"));

            insertSql.append(columnName);
            valueSql.append(":").append(columnName);

            if(i == tableConfigFromDB.size()-1){
                insertSql.append(")\n");
                valueSql.append(")\n");
            }else{
                insertSql.append(",\n");
                valueSql.append(",\n");
            }


            i++;
        }
        return insertSql.toString() + valueSql.toString();
    }

    public static void delete(String dbKey, String sql, Map<String, Object> paramMap) {
        update(dbKey, sql, paramMap);
    }

    public static void delete(String dbKey, String sql) {
        delete(dbKey, sql, null);
    }

    public static void update(String dbKey, String sql) {
        update(dbKey, sql, null);
    }

    public static void update(String dbKey, String sql, Map<String, Object> paramMap) {

        JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);

        if (null != paramMap && paramMap.size() > 0) {
            NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
            namedParameterJdbcTemplate.update(sql, paramMap);
        } else {
            jdbcTemplate.update(sql);
        }
    }
}
